<?php
#read file data
$data = "";
$file = file_get_contents("folkmangd.csv");

#parse data
$lines = explode("\n",$file);
$provinces = array();
foreach ($lines as $line) {
 $cols = explode(",",$line);
 if (strlen($cols[0]) > 0 && strlen($cols[0]) < 40 &&
  empty($cols[1]) &&
  empty($cols[2]) &&
  empty($cols[3]) &&
  empty($cols[4]) &&
  empty($cols[5])) {
   #nytt län
   $province = $cols[0];
   if (!is_array($provinces[$province]))
    $provinces[$province] = array();
   } else if (!empty($cols[0]) && 
    !empty($cols[1]) &&
    !empty($cols[2]) &&
    !empty($cols[3]) &&
    !empty($cols[4]) &&
    !empty($cols[5]) && !empty($province)) {
    #kanske ny kommun
    #ny tätort
    $county = $cols[1];
    #$city = $cols[3];
    if (!is_array($provinces[$province][$county]))
     if (!is_array($provinces[$province][$county]))
      $provinces[$province][$county] = array();
     #$provinces[$province][$county][$city] = $city;
 }
}
#print_r($provinces);

#generate SQL
$data = "";
foreach ($provinces as $province=>$counties) {
 $data .= "INSERT INTO provinces (name) VALUES ('$province');\n";
 foreach ($counties as $county=>$cities) {
  $data .= "INSERT INTO counties (name,province_id) VALUES ('$county',(SELECT id FROM provinces WHERE name='$province'));\n";
  #foreach ($cities as $city) {
  # $data .= "INSERT INTO cities (name,county_id) VALUES ('$city',(SELECT id FROM counties WHERE name='$city'));\n";
  #}
 }
}

file_put_contents("countyprovince.sql",$data);
print "\n";
?>
